//The MIT License
//
//Copyright (c) 2009 nodchip
//
//Permission is hereby granted, free of charge, to any person obtaining a copy
//of this software and associated documentation files (the "Software"), to deal
//in the Software without restriction, including without limitation the rights
//to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
//copies of the Software, and to permit persons to whom the Software is
//furnished to do so, subject to the following conditions:
//
//The above copyright notice and this permission notice shall be included in
//all copies or substantial portions of the Software.
//
//THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
//IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
//FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
//AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
//LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
//OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
//THE SOFTWARE.
package tv.dyndns.kishibe.server.database;

import static com.google.common.base.Strings.emptyToNull;
import static tv.dyndns.kishibe.client.constant.Constant.MAX_NUMBER_OF_ANSWERS;
import static tv.dyndns.kishibe.client.constant.Constant.MAX_NUMBER_OF_CHOICES;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.AbstractKeyedHandler;
import org.apache.commons.dbutils.handlers.AbstractListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import tv.dyndns.kishibe.client.constant.Constant;
import tv.dyndns.kishibe.client.game.ProblemGenre;
import tv.dyndns.kishibe.client.game.ProblemType;
import tv.dyndns.kishibe.client.game.RandomFlag;
import tv.dyndns.kishibe.client.packet.NewAndOldProblems;
import tv.dyndns.kishibe.client.packet.PacketBbsResponse;
import tv.dyndns.kishibe.client.packet.PacketBbsThread;
import tv.dyndns.kishibe.client.packet.PacketChatData;
import tv.dyndns.kishibe.client.packet.PacketLinkData;
import tv.dyndns.kishibe.client.packet.PacketProblem;
import tv.dyndns.kishibe.client.packet.PacketProblemCreationLog;
import tv.dyndns.kishibe.client.packet.PacketProblemMinimum;
import tv.dyndns.kishibe.client.packet.PacketRankingData;
import tv.dyndns.kishibe.client.packet.PacketThemeModeEditor;
import tv.dyndns.kishibe.client.packet.PacketThemeModeEditor.ThemeModeEditorStatus;
import tv.dyndns.kishibe.client.packet.PacketUserData;
import tv.dyndns.kishibe.client.packet.PacketUserData.WebSocketUsage;
import tv.dyndns.kishibe.client.packet.PacketWrongAnswer;
import tv.dyndns.kishibe.server.PageView;
import tv.dyndns.kishibe.server.util.IntArray;

import com.google.common.annotations.VisibleForTesting;
import com.google.common.base.Strings;
import com.google.common.collect.ImmutableSet;
import com.google.gwt.thirdparty.guava.common.collect.Sets;
import com.google.inject.Inject;

public class DirectDatabase implements Database {
	private static final int MAX_PLAYER_ANSWER_LENGTH = 255;
	private final Database cachedDatabase;
	@VisibleForTesting
	final QueryRunner runner;
	private final FullTextSearch fullTextSearch;

	@Inject
	public DirectDatabase(DatabaseConnectionPool databaseConnectionPool, Database cachedDatabase,
			FullTextSearch fullTextSearch) {
		this.runner = new QueryRunner(databaseConnectionPool.getDataSoure());
		this.cachedDatabase = cachedDatabase;
		this.fullTextSearch = fullTextSearch;
	}

	@Override
	public void addProblemIdsToReport(int userCode, List<Integer> problemIds) throws SQLException {
		List<Object[]> params = new ArrayList<Object[]>();
		for (int problemId : problemIds) {
			params.add(new Object[] { userCode, problemId });
		}
		runner.batch("INSERT IGNORE INTO report_problem (USER_CODE, PROBLEM_ID) VALUES (?, ?)",
				params.toArray(new Object[0][]));
	}

	@Override
	public void removeProblemIdFromReport(int userCode, int problemID) throws SQLException {
		runner.update("DELETE IGNORE FROM report_problem WHERE USER_CODE = ? AND PROBLEM_ID = ?",
				userCode, problemID);
	}

	@Override
	public void clearProblemIdFromReport(int userCode) throws SQLException {
		runner.update("DELETE IGNORE FROM report_problem WHERE USER_CODE = ?", userCode);
	}

	@Override
	public List<PacketProblem> getUserProblemReport(int userCode) throws SQLException {
		return runner
				.query("SELECT * FROM problem, report_problem WHERE report_problem.USER_CODE = ? AND problem.ID = report_problem.PROBLEM_ID",
						problemHandler, userCode);
	}

	private static final ResultSetHandler<List<PacketProblem>> problemHandler = new AbstractListHandler<PacketProblem>() {
		@Override
		protected PacketProblem handleRow(ResultSet rs) throws SQLException {
			return toPacketProblem(rs);
		}
	};

	private static PacketProblem toPacketProblem(ResultSet resultSet) throws SQLException {
		PacketProblem data = new PacketProblem();
		data.id = resultSet.getInt("problem.ID");
		data.genre = ProblemGenre.values()[resultSet.getInt("problem.GENRE")];
		data.type = ProblemType.values()[resultSet.getInt("problem.TYPE")];
		data.sentence = resultSet.getString("problem.SENTENCE");
		data.answers = new String[MAX_NUMBER_OF_ANSWERS];
		data.choices = new String[MAX_NUMBER_OF_CHOICES];
		for (int i = 0; i < MAX_NUMBER_OF_ANSWERS; ++i) {
			data.answers[i] = emptyToNull(resultSet.getString("problem.ANSWER" + i));
			data.choices[i] = emptyToNull(resultSet.getString("problem.CHOICE" + i));
		}
		data.good = resultSet.getInt("problem.GOOD");
		data.bad = resultSet.getInt("problem.BAD");
		data.creator = resultSet.getString("problem.CREATER");
		data.note = resultSet.getString("problem.NOTE");
		data.imageAnswer = resultSet.getBoolean("IMAGE_ANSWER");
		data.imageChoice = resultSet.getBoolean("IMAGE_CHOICE");
		data.randomFlag = RandomFlag.values()[resultSet.getInt("problem.RANDOM_FLAG")];
		data.voteGood = resultSet.getInt("problem.VOTE_GOOD");
		data.voteBad = resultSet.getInt("problem.VOTE_BAD");
		data.imageUrl = resultSet.getString("problem.IMAGE_URL");
		data.movieUrl = resultSet.getString("problem.MOVIE_URL");
		// prepareShuffledData()はGame側で呼び出す
		return data;
	}

	private static PacketProblemMinimum toPacketProblemMinimum(ResultSet resultSet)
			throws SQLException {
		PacketProblemMinimum data = new PacketProblemMinimum();
		data.id = resultSet.getInt("problem.ID");
		data.genre = ProblemGenre.values()[resultSet.getInt("problem.GENRE")];
		data.type = ProblemType.values()[resultSet.getInt("problem.TYPE")];
		data.good = resultSet.getInt("problem.GOOD");
		data.bad = resultSet.getInt("problem.BAD");
		data.randomFlag = RandomFlag.values()[resultSet.getInt("problem.RANDOM_FLAG")];
		data.creatorHash = resultSet.getString("problem.CREATER").hashCode();
		return data;
	}

	@VisibleForTesting
	static final ResultSetHandler<PacketUserData> userDataHandler = new ResultSetHandler<PacketUserData>() {
		@Override
		public PacketUserData handle(ResultSet rs) throws SQLException {
			PacketUserData data = new PacketUserData();
			while (rs.next()) {
				data.userCode = rs.getInt("USER_CODE");
				data.playerName = rs.getString("NAME");
				data.greeting = rs.getString("GREETING");
				data.highScore = rs.getInt("HIGH_SCORE");
				data.averageScore = rs.getInt("AVERAGE_SCORE");
				data.playCount = rs.getInt("PLAY_COUNT");
				data.rating = rs.getInt("VICTORY_POINT");
				data.levelName = rs.getInt("LEVEL_NAME");
				data.levelNumber = rs.getInt("LEVEL_NUMBER");
				data.averageRank = rs.getFloat("AVERAGE_RANK");
				data.genres = ProblemGenre.fromBitFlag(rs.getInt("GENRE"));
				data.types = ProblemType.fromBitFlag(rs.getInt("TYPE"));
				data.classLevel = rs.getInt("CLASS_LEVEL");
				data.imageFileName = rs.getString("IMAGE_FILE_NAME");
				data.playSound = rs.getBoolean("PLAY_SOUND");
				data.multiGenre = rs.getBoolean("MULTI_GENRE");
				data.multiType = rs.getBoolean("MULTI_TYPE");
				data.difficultSelect = rs.getInt("DIFFICULT_SELECT");
				data.rankingMove = rs.getBoolean("RANKING_MOVE");
				data.bbsDispInfo = rs.getInt("BBS_DISP_INFO");
				data.bbsAge = rs.getBoolean("BBS_AGE");
				data.timerMode = rs.getInt("TIMER_MODE");
				data.prefecture = rs.getInt("PREFECTURE");
				data.chat = rs.getBoolean("CHAT");
				data.newAndOldProblems = NewAndOldProblems.values()[rs.getInt("NEW_AND_OLD")];
				data.publicEvent = rs.getBoolean("PUBLIC_EVENT");
				data.hideAnswer = rs.getBoolean("HIDE_ANSWER");
				data.showInfo = rs.getBoolean("SHOW_INFO");
				data.reflectEventResult = rs.getBoolean("REFLECT_EVENT_RESULT");
				data.webSocketUsage = WebSocketUsage.values()[rs.getInt("WEB_SOCKET_USAGE")];
				data.volatility = rs.getInt("VOLATILITY");
				data.qwerty = rs.getBoolean("QWERTY");
				data.openId = rs.getString("OPEN_ID");

				String correctCountCsv = rs.getString("CORRECT_COUNT");
				if (correctCountCsv == null) {
					continue;
				}

				int[][][] cc = new int[ProblemGenre.values().length][ProblemType.values().length][2];
				String[] rows = correctCountCsv.split("\n");
				for (int rowIndex = 0; rowIndex < rows.length; ++rowIndex) {
					String[] elements = rows[rowIndex].split(",");

					// 出題形式
					int randomElementIndex = elements.length - ProblemType.numberOfRandoms * 2;
					for (int elementIndex = 0; elementIndex < randomElementIndex; ++elementIndex) {
						int columnIndex = elementIndex / 2;
						int goodBad = elementIndex % 2;
						cc[rowIndex][columnIndex][goodBad] = Integer
								.parseInt(elements[elementIndex]);
					}

					// ランダム
					for (int elementIndex = randomElementIndex; elementIndex < elements.length; ++elementIndex) {
						int columnIndex = ProblemType.values().length
								- (elements.length - elementIndex + 1) / 2;
						int goodBad = elementIndex % 2;
						cc[rowIndex][columnIndex][goodBad] = Integer
								.parseInt(elements[elementIndex]);
					}
				}
				data.correctCount = cc;
			}
			return data;
		}
	};

	private static final ResultSetHandler<int[][][]> correctCountHandler = new ResultSetHandler<int[][][]>() {
		@Override
		public int[][][] handle(ResultSet rs) throws SQLException {
			int[][][] correctCount = new int[ProblemGenre.values().length][ProblemType.values().length][2];
			while (rs.next()) {
				int genre = rs.getInt("GENRE");
				int type = rs.getInt("TYPE");
				int good = rs.getInt("GOOD");
				int bad = rs.getInt("BAD");
				if (genre >= ProblemGenre.values().length || type >= ProblemType.values().length) {
					continue;
				}
				correctCount[genre][type][0] = good;
				correctCount[genre][type][1] = bad;
			}
			return correctCount;
		}
	};

	private void fillUserData(PacketUserData userData) throws SQLException {
		if (userData.correctCount == null) {
			userData.correctCount = runner.query("SELECT * FROM correct_count WHERE USER_CODE = ?",
					correctCountHandler, userData.userCode);
		}

		userData.ignoreUserCodes = Sets.newHashSet(runner.query(
				"SELECT TARGET_USER_CODE FROM ignore_id WHERE USER_CODE = ?",
				new ColumnListHandler<Integer>(Integer.class), userData.userCode));
		userData.ignoreUserCodes.addAll(cachedDatabase.getServerIgnoreUserCode());
	}

	@Override
	public PacketUserData getUserDataByUserCode(int userCode) throws SQLException {
		PacketUserData userData = runner.query("SELECT * FROM player WHERE USER_CODE = ?",
				userDataHandler, userCode);
		fillUserData(userData);
		return userData;
	}

	@Override
	public PacketUserData getUserDataByIdentyfyString(String identifier) throws SQLException {
		PacketUserData userData = runner.query("SELECT * FROM player WHERE OPEN_ID = ?",
				userDataHandler, identifier);
		fillUserData(userData);
		return userData;
	}

	@Override
	public void setUserData(PacketUserData data) throws SQLException {
		// correctCountをcsv形式に変換する
		StringBuilder sb = new StringBuilder();
		for (ProblemGenre genre : ProblemGenre.values()) {
			if (genre.getIndex() != 0) {
				sb.append("\n");
			}

			for (ProblemType type : ProblemType.values()) {
				if (type.getIndex() != 0) {
					sb.append(",");
				}

				sb.append(data.correctCount[genre.getIndex()][type.getIndex()][0])
						.append(",")
						.append(data.correctCount[genre.getIndex()][type.getIndex()][1]);
			}
		}

		runner
				.update("REPLACE INTO player (USER_CODE, NAME, GREETING, HIGH_SCORE, AVERAGE_SCORE, PLAY_COUNT, VICTORY_POINT, LEVEL_NAME, LEVEL_NUMBER, AVERAGE_RANK, GENRE, TYPE, CLASS_LEVEL, IMAGE_FILE_NAME, PLAY_SOUND, MULTI_GENRE, MULTI_TYPE, DIFFICULT_SELECT, RANKING_MOVE, LAST_LOGIN, BBS_DISP_INFO, BBS_AGE, TIMER_MODE, PREFECTURE, CHAT, NEW_AND_OLD, PUBLIC_EVENT, HIDE_ANSWER, SHOW_INFO, REFLECT_EVENT_RESULT, WEB_SOCKET_USAGE, CORRECT_COUNT, VOLATILITY, QWERTY) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
						data.userCode, Strings.nullToEmpty(data.playerName), Strings
								.nullToEmpty(data.greeting), data.highScore, data.averageScore,
						data.playCount, data.rating, data.levelName, data.levelNumber,
						data.averageRank, ProblemGenre.toBitFlag(data.genres), ProblemType
								.toBitFlag(data.types), data.classLevel, Strings
								.isNullOrEmpty(data.imageFileName) ? Constant.ICON_NO_IMAGE
								: data.imageFileName, data.playSound, data.multiGenre,
						data.multiType, data.difficultSelect, data.rankingMove, new Timestamp(
								System.currentTimeMillis()), data.bbsDispInfo, data.bbsAge,
						data.timerMode, data.prefecture, data.chat, data.newAndOldProblems
								.ordinal(), data.publicEvent, data.hideAnswer, data.showInfo,
						data.reflectEventResult, data.webSocketUsage.getIndex(), sb.toString(),
						data.volatility, data.qwerty);
	}

	private static final String KEY_SESSION = "SESSION";
	private static final String KEY_PLAY = "PLAY";
	private static final String KEY_PAGE_VIEW = "page_view";

	@Override
	public PageView loadPageView() throws SQLException {
		return runner.query("SELECT * FROM page_view", pageViewHandler);
	}

	private static final ResultSetHandler<PageView> pageViewHandler = new ResultSetHandler<PageView>() {
		@Override
		public PageView handle(ResultSet resultSet) throws SQLException {
			PageView pageView = new PageView();
			while (resultSet.next()) {
				String type = resultSet.getString("TYPE");
				int number = resultSet.getInt("COUNT");

				if (type.equals(KEY_SESSION)) {
					pageView.numberOfSessions = number;
				} else if (type.equals(KEY_PLAY)) {
					pageView.numberOfPlayers = number;
				} else if (type.equals(KEY_PAGE_VIEW)) {
					pageView.numberOfPageView = number;
				}
			}
			return pageView;
		}
	};

	@Override
	public void savePageView(PageView pageView) throws SQLException {
		List<Object[]> params = new ArrayList<Object[]>();
		params.add(new Object[] { KEY_SESSION, pageView.numberOfSessions });
		params.add(new Object[] { KEY_PLAY, pageView.numberOfPlayers });
		params.add(new Object[] { KEY_PAGE_VIEW, pageView.numberOfPageView });
		runner.batch("REPLACE INTO page_view (TYPE, COUNT) VALUES (?, ?)",
				params.toArray(new Object[0][]));
	}

	@Override
	public Map<String, IntArray> getThemeToProblems(Map<String, List<String>> themeAndQueryStrings)
			throws SQLException {
		return fullTextSearch.getThemeModeProblemMinimums(themeAndQueryStrings);
	}

	private final Object lockAddProblem = new Object();

	// 戻り値は問題番号
	@Override
	public int addProblem(PacketProblem problem) throws SQLException {
		synchronized (lockAddProblem) {
			problem.id = (int) runner.query("SELECT MAX(ID) FROM problem", new ScalarHandler()) + 1;

			String[] answers = Arrays.copyOf(problem.answers, MAX_NUMBER_OF_ANSWERS);
			String[] choices = Arrays.copyOf(problem.choices, MAX_NUMBER_OF_CHOICES);
			for (int i = 0; i < MAX_NUMBER_OF_ANSWERS; ++i) {
				answers[i] = emptyToNull(answers[i]);
				choices[i] = emptyToNull(choices[i]);
			}

			runner
					.update("INSERT INTO problem (ID, GENRE, TYPE, SENTENCE, ANSWER0, ANSWER1, ANSWER2, ANSWER3, ANSWER4, ANSWER5, ANSWER6, ANSWER7, CHOICE0, CHOICE1, CHOICE2, CHOICE3, CHOICE4, CHOICE5, CHOICE6, CHOICE7, GOOD, BAD, CREATER, NOTE, IMAGE_ANSWER, IMAGE_CHOICE, RANDOM_FLAG, IMAGE_URL, MOVIE_URL) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
							problem.id, problem.genre.getIndex(), problem.type.getIndex(),
							problem.sentence, answers[0], answers[1], answers[2], answers[3],
							answers[4], answers[5], answers[6], answers[7], choices[0], choices[1],
							choices[2], choices[3], choices[4], choices[5], choices[6], choices[7],
							problem.good, problem.bad, problem.creator, problem.note,
							problem.imageAnswer, problem.imageChoice,
							problem.randomFlag.getIndex(), problem.imageUrl, problem.movieUrl);

			try {
				fullTextSearch.addProblem(problem);
				return problem.id;
			} catch (Exception e) {
				throw new SQLException(e);
			}
		}
	}

	@Override
	public void updateProblem(PacketProblem problem) throws SQLException {
		String[] answers = Arrays.copyOf(problem.answers, MAX_NUMBER_OF_ANSWERS);
		String[] choices = Arrays.copyOf(problem.choices, MAX_NUMBER_OF_CHOICES);
		for (int i = 0; i < MAX_NUMBER_OF_ANSWERS; ++i) {
			answers[i] = emptyToNull(answers[i]);
			choices[i] = emptyToNull(choices[i]);
		}

		runner
				.update("UPDATE problem SET GENRE = ?, TYPE = ?, SENTENCE = ?, ANSWER0 = ?, ANSWER1 = ?, ANSWER2 = ?, ANSWER3 = ?, ANSWER4 = ?, ANSWER5 = ?, ANSWER6 = ?, ANSWER7 = ?, CHOICE0 = ?, CHOICE1 = ?, CHOICE2 = ?, CHOICE3 = ?, CHOICE4 = ?, CHOICE5 = ?, CHOICE6 = ?, CHOICE7 = ?, GOOD = ?, BAD = ?, CREATER = ?, NOTE = ?, IMAGE_ANSWER = ?, IMAGE_CHOICE = ?, RANDOM_FLAG = ?, VOTE_GOOD = ?, VOTE_BAD = ?, IMAGE_URL = ?, MOVIE_URL = ? WHERE ID = ?",
						problem.genre.getIndex(), problem.type.getIndex(), problem.sentence,
						answers[0], answers[1], answers[2], answers[3], answers[4], answers[5],
						answers[6], answers[7], choices[0], choices[1], choices[2], choices[3],
						choices[4], choices[5], choices[6], choices[7], problem.good, problem.bad,
						problem.creator, problem.note, problem.imageAnswer, problem.imageChoice,
						problem.randomFlag.getIndex(), problem.voteGood, problem.voteBad,
						problem.imageUrl, problem.movieUrl, problem.id);
		try {
			fullTextSearch.updateProblem(problem);
		} catch (Exception e) {
			throw new SQLException(e);
		}
	}

	@Override
	public void updateMinimumProblem(PacketProblemMinimum data) throws SQLException {
		runner
				.update("UPDATE problem SET GENRE = ?, TYPE = ?, GOOD = ?, BAD = ?, RANDOM_FLAG = ? WHERE ID = ?",
						data.genre.getIndex(), data.type.getIndex(), data.good, data.bad,
						data.randomFlag.getIndex(), data.id);
	}

	@Override
	public List<PacketProblem> getProblem(Collection<Integer> ids) throws SQLException {
		if (ids.isEmpty()) {
			return new ArrayList<PacketProblem>();
		}

		StringBuilder sb = new StringBuilder();
		sb.append("SELECT * FROM problem");
		sb.append(" WHERE ID IN (");
		sb.append(concat(ids));
		sb.append(')');
		return runner.query(sb.toString(), problemHandler);
	}

	@Override
	public PacketProblemMinimum getProblemMinimum(int problemId) throws SQLException {
		return runner.query(
				"SELECT ID, GENRE, TYPE, GOOD, BAD, RANDOM_FLAG FROM problem WHERE ID = ?",
				problemMinimumHandler, problemId);
	}

	private static final ResultSetHandler<PacketProblemMinimum> problemMinimumHandler = new ResultSetHandler<PacketProblemMinimum>() {
		@Override
		public PacketProblemMinimum handle(ResultSet rs) throws SQLException {
			if (rs.next()) {
				return toPacketProblemMinimum(rs);
			}
			return null;
		}
	};

	@Override
	public List<PacketProblem> searchProblem(String query, String creator,
			boolean creatorPerfectMatching, Set<ProblemGenre> genres, Set<ProblemType> types,
			Set<RandomFlag> randomFlags) throws SQLException {
		List<Integer> problemIds = fullTextSearch.searchProblem(query, creator,
				creatorPerfectMatching, genres, types, randomFlags);
		return getProblem(problemIds);
	}

	private String concat(Collection<?> objects) {
		StringBuilder sb = new StringBuilder();
		for (Object object : objects) {
			if (sb.length() != 0) {
				sb.append(", ");
			}
			sb.append(object.toString());
		}
		return sb.toString();
	}

	@Override
	public List<PacketProblem> searchSimilarProblemFromDatabase(PacketProblem problem)
			throws SQLException {
		List<Integer> problemIds = fullTextSearch.searchSimilarProblemFromDatabase(problem);
		return getProblem(problemIds);
	}

	@Override
	public void addPlayerAnswers(int problemId, ProblemType type, List<String> answers)
			throws SQLException {
		List<Object[]> params = new ArrayList<Object[]>();
		for (String answer : answers) {
			if (answer.isEmpty()) {
				continue;
			}

			// 線結びの回答整理
			if (type == ProblemType.Senmusubi || type == ProblemType.Tato) {
				String[] split = answer.split(Constant.DELIMITER_GENERAL);
				Arrays.sort(split);

				StringBuilder builder = new StringBuilder();
				for (String s : split) {
					builder.append(s);
					builder.append(Constant.DELIMITER_GENERAL);
				}
				while (builder.charAt(builder.length() - 1) == Constant.DELIMITER_GENERAL.charAt(0)) {
					builder.deleteCharAt(builder.length() - 1);
				}
				answer = builder.toString();
			}

			if (answer.length() > MAX_PLAYER_ANSWER_LENGTH) {
				answer = answer.substring(0, MAX_PLAYER_ANSWER_LENGTH);
			}

			params.add(new Object[] { problemId, answer });
		}
		runner
				.batch("INSERT player_answer (PROBLEM_ID, ANSWER, COUNT) VALUES (?, ?, 1) ON DUPLICATE KEY UPDATE COUNT = COUNT + 1",
						params.toArray(new Object[0][]));
	}

	@Override
	public List<PacketWrongAnswer> getPlayerAnswers(int problemId) throws SQLException {
		return runner.query(
				"SELECT ANSWER, COUNT FROM player_answer WHERE PROBLEM_ID = ? ORDER BY COUNT DESC",
				wrongAnswerHandler, problemId);
	}

	private static final ResultSetHandler<List<PacketWrongAnswer>> wrongAnswerHandler = new AbstractListHandler<PacketWrongAnswer>() {
		@Override
		protected PacketWrongAnswer handleRow(ResultSet rs) throws SQLException {
			PacketWrongAnswer wrongAnswer = new PacketWrongAnswer();
			wrongAnswer.answer = rs.getString("ANSWER");
			wrongAnswer.count = rs.getInt("COUNT");
			return wrongAnswer;
		}
	};

	@Override
	public void removePlayerAnswers(int problemId) throws SQLException {
		runner.update("DELETE FROM player_answer WHERE PROBLEM_ID = ?", problemId);
	}

	private static final String[] RANKING_QUERIES = {
			"SELECT USER_CODE, NAME, IMAGE_FILE_NAME, HIGH_SCORE AS DATA FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?",
			"SELECT USER_CODE, NAME, IMAGE_FILE_NAME, AVERAGE_SCORE AS DATA FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?",
			"SELECT USER_CODE, NAME, IMAGE_FILE_NAME, PLAY_COUNT AS DATA FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?",
			"SELECT USER_CODE, NAME, IMAGE_FILE_NAME, VICTORY_POINT AS DATA FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?",
			"SELECT USER_CODE, NAME, IMAGE_FILE_NAME, AVERAGE_RANK AS DATA FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? ORDER BY DATA ASC LIMIT 0, ?",
			"SELECT USER_CODE, NAME, IMAGE_FILE_NAME, CLASS_LEVEL AS DATA FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?",
			"SELECT player.USER_CODE, player.NAME, player.IMAGE_FILE_NAME, correct_count.GOOD / (correct_count.GOOD + correct_count.BAD) AS DATA FROM player, correct_count WHERE player.USER_CODE = correct_count.USER_CODE AND correct_count.GENRE = 0 AND correct_count.TYPE = 0 AND player.PLAY_COUNT >= 10 AND player.LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?", };

	@Override
	public List<List<PacketRankingData>> getRankingData() throws SQLException {
		Timestamp timestamp = new Timestamp(System.currentTimeMillis()
				- (long) Constant.RANKING_DISPLAY_DAY * 24 * 60 * 60 * 1000);
		List<List<PacketRankingData>> result = new ArrayList<List<PacketRankingData>>();
		for (String rankingQuery : RANKING_QUERIES) {
			result.add(runner.query(rankingQuery, rankingDataHandler, timestamp,
					Constant.NUMBER_OF_RANKING_DATA));
		}
		return result;
	}

	@Override
	public Map<String, List<PacketRankingData>> getThemeModeRankingData() throws SQLException {
		Timestamp timestamp = new Timestamp(System.currentTimeMillis()
				- (long) Constant.RANKING_DISPLAY_DAY * 24 * 60 * 60 * 1000);
		Map<String, List<PacketRankingData>> result = new HashMap<String, List<PacketRankingData>>();
		for (String theme : getThemeModeQueries().keySet()) {
			result.put(theme, runner.query(
					"SELECT player.USER_CODE, NAME, IMAGE_FILE_NAME, SCORE AS DATA "
							+ "FROM player, theme_mode_score "
							+ "WHERE theme_mode_score.theme = ? "
							+ "AND player.USER_CODE = theme_mode_score.USER_CODE "
							+ "AND player.PLAY_COUNT >= 10 " + "AND player.LAST_LOGIN >= ? "
							+ "ORDER BY DATA " + "DESC LIMIT 0, ?", rankingDataHandler, theme,
					timestamp, Constant.NUMBER_OF_RANKING_DATA));
		}
		return result;
	}

	private static final ResultSetHandler<List<PacketRankingData>> rankingDataHandler = new AbstractListHandler<PacketRankingData>() {
		@Override
		protected PacketRankingData handleRow(ResultSet rs) throws SQLException {
			PacketRankingData rankingData = new PacketRankingData();
			rankingData.userCode = rs.getInt("USER_CODE");
			rankingData.name = rs.getString("NAME");
			rankingData.imageFileName = rs.getString("IMAGE_FILE_NAME");
			rankingData.data = rs.getString("DATA");
			return rankingData;
		}
	};

	@Override
	public void addCreationLog(int problemID, int userCode, String machineIP) throws SQLException {
		runner
				.update("INSERT INTO creation_log (PROBLEM_ID, USER_CODE, DATE, MACHINE_IP) VALUES (?, ?, ?, ?)",
						problemID, userCode, new Timestamp(System.currentTimeMillis()), machineIP);
	}

	@Override
	public void addIgnoreUserCode(int userCode, int targetUserCode) throws SQLException {
		runner.update("INSERT INTO ignore_id (USER_CODE, TARGET_USER_CODE) VALUES (?, ?)",
				userCode, targetUserCode);
	}

	@Override
	public void removeIgnoreUserCode(int userCode, int targetUserCode) throws SQLException {
		runner.update("DELETE FROM ignore_id WHERE USER_CODE = ? AND TARGET_USER_CODE = ?",
				userCode, targetUserCode);
	}

	@Override
	public void addServerIgnoreUserCode(int userCode) throws SQLException {
		runner.update("INSERT IGNORE INTO ignore_id (USER_CODE, TARGET_USER_CODE) VALUES (?, ?)",
				0, userCode);
	}

	@Override
	public Set<Integer> getServerIgnoreUserCode() throws SQLException {
		return ImmutableSet.copyOf(runner.query(
				"SELECT TARGET_USER_CODE FROM ignore_id WHERE USER_CODE = ?",
				serverIgnoreUserCodeHandler, 0));
	}

	private static final ResultSetHandler<List<Integer>> serverIgnoreUserCodeHandler = new AbstractListHandler<Integer>() {
		@Override
		protected Integer handleRow(ResultSet rs) throws SQLException {
			return rs.getInt("TARGET_USER_CODE");
		}
	};

	@Override
	public void addChatLog(PacketChatData data) throws SQLException {
		runner
				.update("INSERT IGNORE INTO chat_log (DATE, NAME, BODY, CLASS_LEVEL, USER_CODE, MACHINE_IP) VALUES (?, ?, ?, ?, ?, ?)",
						new Timestamp(System.currentTimeMillis()), data.name, data.body,
						data.classLevel, data.userCode, data.remoteAddress);
	}

	@Override
	public Map<Integer, PacketChatData> getLatestChatData() throws SQLException {
		Map<Integer, PacketChatData> result = runner.query(
				"SELECT * FROM chat_log ORDER BY RES_ID DESC LIMIT 100", latestChatDataHandler);
		for (PacketChatData chatData : result.values()) {
			chatData.imageFileName = cachedDatabase.getUserDataByUserCode(chatData.userCode).imageFileName;
		}
		return result;
	}

	private static final AbstractKeyedHandler<Integer, PacketChatData> latestChatDataHandler = new AbstractKeyedHandler<Integer, PacketChatData>() {
		@Override
		protected PacketChatData createRow(ResultSet rs) throws SQLException {
			PacketChatData data = new PacketChatData();
			data.resId = rs.getInt("RES_ID");
			data.date = rs.getTimestamp("DATE").getTime();
			data.name = rs.getString("NAME");
			data.body = rs.getString("BODY");
			data.classLevel = rs.getInt("CLASS_LEVEL");
			data.userCode = rs.getInt("USER_CODE");
			data.remoteAddress = rs.getString("MACHINE_IP");
			return data;
		}

		@Override
		protected Integer createKey(ResultSet rs) throws SQLException {
			return rs.getInt("RES_ID");
		}
	};

	@Override
	public List<PacketProblemCreationLog> getProblemCreationHistory(int problemId)
			throws SQLException {
		return runner
				.query("SELECT player.NAME, creation_log.USER_CODE, creation_log.DATE, creation_log.MACHINE_IP FROM player, creation_log WHERE creation_log.PROBLEM_ID = ? AND creation_log.USER_CODE = player.USER_CODE",
						problemCreationLogHandler, problemId);
	}

	private static final ResultSetHandler<List<PacketProblemCreationLog>> problemCreationLogHandler = new AbstractListHandler<PacketProblemCreationLog>() {
		@Override
		protected PacketProblemCreationLog handleRow(ResultSet rs) throws SQLException {
			PacketProblemCreationLog history = new PacketProblemCreationLog();
			history.name = rs.getString("player.NAME");
			history.userCode = rs.getInt("creation_log.USER_CODE");
			history.date = new Date(rs.getTimestamp("creation_log.DATE").getTime());
			history.ip = rs.getString("creation_log.MACHINE_IP");
			return history;
		}
	};

	@Override
	public List<PacketBbsThread> getBbsThread(int start, int count) throws SQLException {
		return runner.query("SELECT id, title FROM bbs_thread ORDER BY lastUpdate DESC LIMIT ?, ?",
				bbsThreadHandler, start, count);
	}

	private static final ResultSetHandler<List<PacketBbsThread>> bbsThreadHandler = new AbstractListHandler<PacketBbsThread>() {
		@Override
		protected PacketBbsThread handleRow(ResultSet rs) throws SQLException {
			PacketBbsThread thread = new PacketBbsThread();
			thread.id = rs.getLong("id");
			thread.title = rs.getString("title");
			return thread;
		}
	};

	private static final ResultSetHandler<List<PacketBbsResponse>> handlerBbsResponse = new AbstractListHandler<PacketBbsResponse>() {
		@Override
		protected PacketBbsResponse handleRow(ResultSet rs) throws SQLException {
			PacketBbsResponse response = new PacketBbsResponse();
			response.id = rs.getLong("id");
			response.threadId = rs.getLong("threadId");
			response.name = rs.getString("name");
			response.userCode = rs.getInt("userCode");
			response.remoteAddress = rs.getString("userCode");
			response.dispInfo = rs.getInt("dispInfo");
			response.postTime = rs.getLong("postTime");
			response.body = rs.getString("body");
			return response;
		}
	};

	@Override
	public List<PacketBbsResponse> getBbsResponses(int threadId, int count) throws SQLException {
		List<PacketBbsResponse> list = new ArrayList<PacketBbsResponse>();
		list.addAll(runner.query(
				"SELECT * FROM bbs_response WHERE threadId = ? ORDER BY id DESC LIMIT 0, ?",
				handlerBbsResponse, threadId, count));
		list.addAll(runner.query(
				"SELECT * FROM bbs_response WHERE threadId = ? ORDER BY id ASC LIMIT 0, 1",
				handlerBbsResponse, threadId));
		Collections.reverse(list);
		return list;
	}

	private final Object lockBuildBbsThread = new Object();

	@Override
	public void buildBbsThread(PacketBbsThread thread, PacketBbsResponse response)
			throws SQLException {
		synchronized (lockBuildBbsThread) {
			runner.update("INSERT INTO bbs_thread (lastUpdate, title) VALUES (?, ?)",
					System.currentTimeMillis(), thread.title);
			thread.id = (long) runner.query("SELECT LAST_INSERT_ID() FROM bbs_thread",
					new ScalarHandler());
			runner
					.update("INSERT INTO bbs_response (threadId, name, userCode, machineIp, dispInfo, postTime, body) VALUES (?, ?, ?, ?, ?, ?, ?)",
							response.threadId, response.name, response.userCode,
							response.remoteAddress, response.dispInfo, System.currentTimeMillis(),
							response.body);
		}
	}

	@Override
	public void writeToBbs(PacketBbsResponse response, boolean age) throws SQLException {
		runner
				.update("INSERT INTO bbs_response (threadId, name, userCode, machineIp, dispInfo, postTime, body) VALUES (?, ?, ?, ?, ?, ?, ?)",
						response.threadId, response.name, response.userCode,
						response.remoteAddress, response.dispInfo, System.currentTimeMillis(),
						response.body);
		if (age) {
			runner.update("UPDATE bbs_thread SET lastUpdate = ? WHERE id = ?",
					System.currentTimeMillis(), response.threadId);
		}
	}

	@Override
	public int getNumberOfBbsThread() throws SQLException {
		return (int) (long) runner.query("SELECT COUNT(*) FROM bbs_thread", new ScalarHandler());
	}

	@Override
	public boolean isUsedUserCode(int userCode) throws SQLException {
		return 0 < (long) runner.query("SELECT COUNT(*) FROM player WHERE USER_CODE = ?",
				new ScalarHandler(), userCode);
	}

	@Override
	public void addLinkData(PacketLinkData linkData) throws SQLException {
		runner
				.update("INSERT INTO link (lastUpdate, homePageName, authorName, url, bannerUrl, description, userCode) VALUES (?, ?, ?, ?, ?, ?, ?)",
						System.currentTimeMillis(), linkData.homePageName, linkData.authorName,
						linkData.url, linkData.bannerUrl, linkData.description, linkData.userCode);
	}

	@Override
	public void updateLinkData(PacketLinkData linkData) throws SQLException {
		runner
				.update("UPDATE link SET lastUpdate = ?, homePageName = ?, authorName = ?, url = ?, bannerUrl = ?, description = ?, userCode = ? WHERE id = ?",
						System.currentTimeMillis(), linkData.homePageName, linkData.authorName,
						linkData.url, linkData.bannerUrl, linkData.description, linkData.userCode,
						linkData.id);
	}

	@Override
	public void removeLinkData(int id) throws SQLException {
		runner.update("UPDATE link SET valid = FALSE WHERE id = ?", id);
	}

	@Override
	public List<PacketLinkData> getLinkDatas(int start, int count) throws SQLException {
		return runner.query(
				"SELECT * FROM link WHERE valid = TRUE ORDER BY lastUpdate DESC LIMIT ?, ?",
				linkDataHandler, start, count);
	}

	private static final ResultSetHandler<List<PacketLinkData>> linkDataHandler = new AbstractListHandler<PacketLinkData>() {
		@Override
		protected PacketLinkData handleRow(ResultSet rs) throws SQLException {
			PacketLinkData linkData = new PacketLinkData();
			linkData.id = (int) rs.getLong("id");
			linkData.lastUpdate = rs.getLong("lastUpdate");
			linkData.homePageName = rs.getString("homePageName");
			linkData.authorName = rs.getString("authorName");
			linkData.url = rs.getString("url");
			linkData.bannerUrl = rs.getString("bannerUrl");
			linkData.description = rs.getString("description");
			linkData.userCode = rs.getInt("userCode");
			return linkData;
		}
	};

	@Override
	public int getNumberOfLinkDatas() throws SQLException {
		return (int) (long) runner.query("SELECT COUNT(*) FROM link WHERE valid = TRUE",
				new ScalarHandler());
	}

	@Override
	public int getNumberOfActiveUsers() throws SQLException {
		long time = System.currentTimeMillis() - (long) Constant.RANKING_DISPLAY_DAY * 24 * 60 * 60
				* 1000;
		return (int) (long) runner.query(
				"SELECT COUNT(*) FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ?",
				new ScalarHandler(), time);
	}

	@Override
	public Map<Integer, List<Integer>> getRatingGroupedByPrefecture() throws SQLException {
		long time = System.currentTimeMillis() - (long) Constant.RANKING_DISPLAY_DAY * 24 * 60 * 60
				* 1000;
		return runner
				.query("SELECT VICTORY_POINT, PREFECTURE FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? AND PREFECTURE != 0",
						ratingGroupedByPrefectureHandler, time);
	}

	private static final ResultSetHandler<Map<Integer, List<Integer>>> ratingGroupedByPrefectureHandler = new ResultSetHandler<Map<Integer, List<Integer>>>() {
		@Override
		public Map<Integer, List<Integer>> handle(ResultSet resultSet) throws SQLException {
			Map<Integer, List<Integer>> result = new HashMap<Integer, List<Integer>>();
			while (resultSet.next()) {
				int rating = resultSet.getInt("VICTORY_POINT");
				int prefecture = resultSet.getInt("PREFECTURE");

				if (!result.containsKey(prefecture)) {
					result.put(prefecture, new ArrayList<Integer>());
				}
				result.get(prefecture).add(rating);
			}
			return result;
		}
	};

	@Override
	public void addRatingHistory(int userCode, int rating) throws SQLException {
		runner.update("INSERT INTO rating_history (USER_CODE, TIME, RATING) VALUES (?, ?, ?)",
				userCode, System.currentTimeMillis(), rating);
	}

	@Override
	public List<Integer> getRatingHistory(int userCode) throws SQLException {
		return runner
				.query("SELECT RATING FROM rating_history WHERE USER_CODE = ? ORDER BY TIME DESC LIMIT 0, ?",
						new ColumnListHandler<Integer>(Integer.class), userCode,
						Constant.MAX_RATING_HISTORY);
	}

	@Override
	public List<Integer> getWholeRating() throws SQLException {
		long time = System.currentTimeMillis() - (long) Constant.RANKING_DISPLAY_DAY * 24 * 60 * 60
				* 1000;
		return runner.query(
				"SELECT VICTORY_POINT FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ?",
				new ColumnListHandler<Integer>(Integer.class), time);
	}

	@Override
	public Map<String, List<String>> getThemeModeQueries() throws SQLException {
		return runner.query("SELECT * FROM theme_mode", themeModeQueriesHandler);
	}

	private static final ResultSetHandler<Map<String, List<String>>> themeModeQueriesHandler = new ResultSetHandler<Map<String, List<String>>>() {
		@Override
		public Map<String, List<String>> handle(ResultSet resultSet) throws SQLException {
			Map<String, List<String>> queries = new HashMap<String, List<String>>();
			while (resultSet.next()) {
				String theme = resultSet.getString("THEME");
				String query = resultSet.getString("QUERY");

				if (!queries.containsKey(theme)) {
					queries.put(theme, new ArrayList<String>());
				}

				queries.get(theme).add(query);
			}
			return queries;
		}
	};

	@Override
	public void addThemeModeQuery(String theme, String query) throws SQLException {
		runner.update("REPLACE theme_mode (THEME, QUERY) VALUES (?, ?)", theme, query);
	}

	@Override
	public void removeThemeModeQuery(String theme, String query) throws SQLException {
		runner.update("DELETE FROM theme_mode WHERE THEME = ? AND QUERY = ?", theme, query);
	}

	@Override
	public void updateThemeModeScore(int userCode, String theme, int score) throws SQLException {
		runner
				.update("INSERT LOW_PRIORITY theme_mode_score (THEME, USER_CODE, SCORE) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE SCORE = ((SCORE < ?) * ? + (SCORE > ?) * SCORE)",
						theme, userCode, score, score, score, score);
	}

	@Override
	public void voteToProblem(int problemId, boolean good, String feedback) throws SQLException {
		int voteGood = good ? 1 : 0;
		int voteBad = good ? 0 : 1;
		runner
				.update("UPDATE problem SET VOTE_GOOD = VOTE_GOOD + ?, VOTE_BAD = VOTE_BAD + ? WHERE ID = ?",
						voteGood, voteBad, problemId);
		runner.update("INSERT problem_questionnaire (problemId, text, date) VALUES (?, ?, ?)",
				problemId, feedback, System.currentTimeMillis());
	}

	@Override
	public void clearProblemFeedback(int problemId) throws SQLException {
		runner.update("UPDATE problem_questionnaire SET deleted = TRUE WHERE problemId = ?",
				problemId);
	}

	@Override
	public List<String> getProblemFeedback(int problemId) throws SQLException {
		return runner.query(
				"SELECT text FROM problem_questionnaire WHERE problemId = ? && deleted = FALSE",
				new ColumnListHandler<String>(String.class), problemId);
	}

	@Override
	public void processProblems(final ProblemProcessable processor) throws SQLException {
		runner.query("SELECT * FROM problem", new ResultSetHandler<Void>() {
			@Override
			public Void handle(ResultSet rs) throws SQLException {
				int counter = 0;
				while (rs.next()) {
					PacketProblem data = toPacketProblem(rs);

					try {
						processor.process(data);
					} catch (Exception e) {
						e.printStackTrace();
					}

					if (++counter % 10000 == 0) {
						System.err.println(counter);
					}
				}

				return null;
			}
		});
	}

	@Override
	public void processProblemMinimums(final ProblemMinimumProcessable processer)
			throws SQLException {
		String sql = "SELECT ID, GENRE, TYPE, GOOD, BAD, RANDOM_FLAG, CREATER FROM problem";
		runner.query(sql, new ResultSetHandler<Void>() {
			@Override
			public Void handle(ResultSet rs) throws SQLException {
				int counter = 0;
				while (rs.next()) {
					PacketProblemMinimum problem = toPacketProblemMinimum(rs);

					try {
						processer.process(problem);
					} catch (Exception e) {
						e.printStackTrace();
					}

					if (++counter % 10000 == 0) {
						System.err.println(counter);
					}
				}

				return null;
			}
		});
	}

	@Override
	public List<PacketProblem> getLastestProblems() throws SQLException {
		return runner.query("SELECT * FROM problem ORDER BY ID DESC LIMIT 0, 100", problemHandler);
	}

	@Override
	public List<PacketThemeModeEditor> getThemeModeEditors() throws SQLException {
		return runner.query("SELECT player.USER_CODE, player.NAME, theme_mode_editor.status "
				+ "FROM theme_mode_editor, player "
				+ "WHERE theme_mode_editor.userCode = player.USER_CODE "
				+ "ORDER BY theme_mode_editor.userCode ASC, theme_mode_editor.status DESC",
				themeModeEditorsHandler);
	}

	private static final ResultSetHandler<List<PacketThemeModeEditor>> themeModeEditorsHandler = new AbstractListHandler<PacketThemeModeEditor>() {
		@Override
		protected PacketThemeModeEditor handleRow(ResultSet rs) throws SQLException {
			PacketThemeModeEditor editor = new PacketThemeModeEditor();
			editor.userCode = rs.getInt("player.USER_CODE");
			editor.name = rs.getString("player.NAME");
			editor.themeModeEditorStatus = ThemeModeEditorStatus.values()[rs
					.getInt("theme_mode_editor.status")];
			return editor;
		}
	};

	@Override
	public ThemeModeEditorStatus getThemeModeEditorsStatus(int userCode) throws SQLException {
		return runner.query("SELECT status FROM theme_mode_editor WHERE userCode = ?",
				themeModeEditorsStatusHandler, userCode);
	}

	private static final ResultSetHandler<ThemeModeEditorStatus> themeModeEditorsStatusHandler = new ResultSetHandler<ThemeModeEditorStatus>() {
		@Override
		public ThemeModeEditorStatus handle(ResultSet resultSet) throws SQLException {
			if (resultSet.next()) {
				return ThemeModeEditorStatus.values()[resultSet.getInt("status")];
			}
			return null;
		}
	};

	@Override
	public void updateThemeModeEdtorsStatus(int userCode, ThemeModeEditorStatus status)
			throws SQLException {
		runner.update("REPLACE theme_mode_editor (userCode, status) VALUES (?, ?)", userCode,
				status.ordinal());
	}

	@Override
	public String getPassword(String type) throws SQLException {
		return (String) runner.query("SELECT password FROM password WHERE type = ?",
				new ScalarHandler(), type);
	}

	// 制限ユーザー
	@Override
	public Set<Integer> getLimitedUserCodes() throws SQLException {
		return ImmutableSet.copyOf(runner.query(
				"SELECT USER_CODE FROM limited_user_code WHERE VALID = TRUE",
				new ColumnListHandler<Integer>(Integer.class)));
	}

	@Override
	public Set<String> getLimitedRemoteAddresses() throws SQLException {
		return ImmutableSet.copyOf(runner.query("SELECT IP FROM limited_ip WHERE VALID = TRUE",
				new ColumnListHandler<String>(String.class)));
	}

	@Override
	public void addLimitedRemoteAddress(String remoteAddress) throws SQLException {
		runner.update("REPLACE limited_ip (IP, VALID) VALUES (?, TRUE)", remoteAddress);
	}

	@Override
	public void addLimitedUserCode(int userCode) throws SQLException {
		runner.update("REPLACE limited_user_code (USER_CODE, VALID) VALUES (?, TRUE)", userCode);
	}

	@Override
	public void removeLimitedUserCode(int userCode) throws SQLException {
		runner.update("UPDATE limited_user_code SET VALID = FALSE WHERE USER_CODE = ?", userCode);
	}

	@Override
	public void removeLimitedRemoteAddress(String remoteAddress) throws SQLException {
		runner.update("UPDATE limited_ip SET VALID = FALSE WHERE IP = ?", remoteAddress);
	}

	@Override
	public void clearLimitedUserCodes() throws SQLException {
		runner.update("UPDATE limited_user_code SET VALID = FALSE");
	}

	@Override
	public void clearLimitedRemoteAddresses() throws SQLException {
		runner.update("UPDATE limited_ip SET VALID = FALSE");
	}

	@Override
	public int getNumberOfChatLog() throws SQLException {
		return (int) (long) runner.query("SELECT COUNT(*) FROM chat_log", new ScalarHandler());
	}

	@Override
	public int getChatLogId(int year, int month, int day, int hour, int minute, int second)
			throws SQLException {
		String s = String.format("%04d-%02d-%02d %02d:%02d:%02d", year, month, day, hour, minute,
				second);
		return (int) runner.query("SELECT RES_ID FROM chat_log WHERE ? <= DATE LIMIT 1",
				new ScalarHandler(), s);
	}

	@Override
	public List<PacketChatData> getChatLog(int start) throws SQLException {
		// ORDER BYを使うと精査が入って遅くなる
		return runner
				.query("SELECT * FROM chat_log c, player p WHERE c.RES_ID >= ? AND c.USER_CODE = p.USER_CODE LIMIT ?",
						chatLogHandler, start, Constant.CHAT_MAX_RESPONSES);
	}

	private static final ResultSetHandler<List<PacketChatData>> chatLogHandler = new AbstractListHandler<PacketChatData>() {
		@Override
		protected PacketChatData handleRow(ResultSet rs) throws SQLException {
			PacketChatData data = new PacketChatData();
			data.resId = rs.getInt("c.RES_ID");
			data.date = rs.getTimestamp("c.DATE").getTime();
			data.name = rs.getString("c.NAME");
			data.body = rs.getString("c.BODY");
			data.classLevel = rs.getInt("c.CLASS_LEVEL");
			data.userCode = rs.getInt("c.USER_CODE");
			data.remoteAddress = rs.getString("c.MACHINE_IP");
			data.imageFileName = rs.getString("p.IMAGE_FILE_NAME");
			return data;
		}
	};

	@Override
	public int getNumberOfCreationLogWithUserCode(int userCode, long dateFrom) throws SQLException {
		return (int) (long) runner.query(
				"SELECT COUNT(*) FROM creation_log WHERE ? < DATE AND USER_CODE = ?",
				new ScalarHandler(), new Timestamp(dateFrom), userCode);
	}

	@Override
	public int getNumberOfCreationLogWithMachineIp(String machineIp, long dateFrom)
			throws SQLException {
		return (int) (long) runner.query(
				"SELECT COUNT(*) FROM creation_log WHERE ? < DATE AND MACHINE_IP = ?",
				new ScalarHandler(), new Timestamp(dateFrom), machineIp);
	}
}
